﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_DeleteRotatingBanner')
BEGIN
    PRINT 'Dropping Procedure proc_cm_DeleteRotatingBanner'
    DROP  Procedure  proc_cm_DeleteRotatingBanner
END
GO

PRINT 'Creating Procedure proc_cm_DeleteRotatingBanner'
GO

CREATE PROCEDURE [dbo].[proc_cm_DeleteRotatingBanner]
	@pRotatingBannerId uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @vPriority INT

	SELECT @vPriority = [priority_num]
	FROM [dbo].[tbl_rotating_banner]
	WHERE [rotating_banner_id] = @pRotatingBannerId

	DELETE FROM [dbo].[tbl_rotating_banner]
	WHERE [rotating_banner_id] = @pRotatingBannerId

	UPDATE [dbo].[tbl_rotating_banner]
	SET [priority_num] = [priority_num] - 1
	WHERE [priority_num] > @vPriority
END
GO

GRANT EXEC ON dbo.proc_cm_DeleteRotatingBanner TO PUBLIC
GO
